package simpleServerThread;

import java.sql.*;
import java.util.AbstractMap;
import java.util.ArrayList;
import java.util.Map.Entry;
import java.lang.Math;

import com.example.clientsideapplication.Merchant;
import com.example.clientsideapplication.Offer;
import com.example.clientsideapplication.Position;

public class DbConnection {

	private String connString;
	private Connection connection;
	
	/* Classe DbConnection */
	public DbConnection(){
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connString = "jdbc:mysql://localhost/geoaddb?" + "user=root&password=capocchia";
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	
	private void OpenDb(){
		try {
			connection = DriverManager.getConnection(connString);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	private void CloseDb(){
		try {
			if(connection != null && connection.isClosed()!=true)
				connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	public boolean InsertOffer(int idMerchant, Offer offer) {
	    PreparedStatement st;
	    boolean insert = true;
		
		try {
			OpenDb();
			
	        st = connection.prepareStatement("INSERT INTO offer (IdMerchant, Category, Product, StartDate, EndDate, InfoProduct, Price) VALUES (?,?,?,?,?,?,?)");
	        st.setInt(1, idMerchant);
	        st.setString(2, offer.getProductCategory());
	        st.setString(3, offer.getProductName());
	        st.setDate(4, offer.getStartDate());
	        st.setDate(5, offer.getEndDate());
	        st.setString(6, offer.getProductInfo());
	        st.setDouble(7, offer.getProductPrice());
	        
	        st.executeUpdate();
	        
	        st.close();
		}
	    catch(SQLException e)
	    {
	    	insert = false;
	    }
		finally
		{
			CloseDb();
		}
		
		return insert;
	}
	
	
	public boolean UpdateOffer(int idMerchant, int idOffer, Offer offer) {
	    PreparedStatement st;
	    boolean update = true;
	    
		try {
			OpenDb();
			
	        st = connection.prepareStatement("UPDATE offer SET IdMerchant=?, Product=?, Price=?, StartDate=?, EndDate=?, InfoProduct=?, Category=? WHERE ID = ? ");
	        
	        st.setInt(1, idMerchant);
	        st.setString(2, offer.getProductName());
	        st.setDouble(3, offer.getProductPrice());
	        st.setDate(4, offer.getStartDate());
	        st.setDate(5, offer.getEndDate());
	        st.setString(6, offer.getProductInfo());
	        st.setString(7, offer.getProductCategory());
	        st.setInt(8, idOffer);
	        
	        st.executeUpdate();
	        
	        st.close();
		}
	    catch(SQLException e)
	    {
	    	update = false;
	    }
		finally
		{
			CloseDb();
		}
		
		return update;
	}
	
	
	public boolean DeleteOffer(int idOffer) {
	    PreparedStatement st;
	    boolean delete = true;
		
		try {
			OpenDb();
			
	        st = connection.prepareStatement("DELETE FROM offer WHERE ID=?");
	        
	        st.setInt(1, idOffer);
	        
	        st.executeUpdate();

	        st.close();
		}
	    catch(SQLException e)
	    {
	    	delete = false;
	    }
		finally
		{
			CloseDb();
		}
		
		return delete;
	}
	
	
	public boolean InsertMerchant(Merchant merchant) {
	    PreparedStatement st;
	    boolean insert = true;
		
		try {
			OpenDb();
			
	        st = connection.prepareStatement("INSERT INTO merchant (user, password, longitudePosition, latitudePosition, ShopName, ShopAddress) VALUES (?, ?, ?, ?, ?, ?)");
	        st.setString(1, merchant.getUsername());
	        st.setString(2, merchant.getPassword());
	        st.setDouble(3, merchant.getLongGps());
	        st.setDouble(4, merchant.getLatGps());
	        st.setString(5, merchant.getShopName());
	        st.setString(6, merchant.getShopAddress());
	        
	        st.executeUpdate();
	        
	        st.close();
		}
	    catch(SQLException e)
	    {
	    	insert = false;
	    }
		finally
		{
			CloseDb();
		}
		
		return insert;
	}
	
	
	public ArrayList<Offer> GetMerchantOffer(int idMerchant) {
	    PreparedStatement st;
	    ResultSet rs;
	    ArrayList<Offer> list = new ArrayList<Offer>();
	    
		try {
			OpenDb();
			
	        st = connection.prepareStatement("SELECT * FROM offer WHERE idMerchant = ?");
	        st.setInt(1, idMerchant);

	        rs = st.executeQuery();
	        
	        while(rs.next())
	        {
	        	Offer singleOffer = new Offer(rs.getInt("ID"),rs.getString("category"), rs.getString("product"), rs.getString("infoProduct"), rs.getDate("startDate"), rs.getDate("endDate"), rs.getDouble("price"));
	        	list.add(singleOffer);
	        }
	        
	        st.close();
		}
	    catch(SQLException e)
	    {
	    	e.printStackTrace();
	    }
		finally
		{
			CloseDb();
		}
		
		
		return list;
	}
	
	
	public int LoginMerchant(String username, String password) {
	    PreparedStatement st;
	    ResultSet rs;
	    int login = -1;
	    
		try {
			OpenDb();
			
	        st = connection.prepareStatement("SELECT ID FROM merchant WHERE User=? AND Password=?");
	        st.setString(1, username);
	        st.setString(2, password);
	        
	        rs = st.executeQuery();
	        
	        if(rs.next()!=false){
	        	login = rs.getInt("ID");
	        }
	        
	        st.close();
		}
	    catch(SQLException e)
	    {
	    	e.printStackTrace();
	    }
		finally
		{
			CloseDb();
		}
		
		return login;
	}
	
	
	public ArrayList<Entry<Merchant, Offer>> GetOfferWithinLimits(Position position, ArrayList<String> preferences, int meters) {
	    PreparedStatement st;
	    ResultSet rs;
	    ArrayList<Entry<Merchant, Offer>> list = new ArrayList<Entry<Merchant, Offer>>();
	    Double myLat, myLon;
	    Double upperLat, upperLon, lowerLat, lowerLon;
	    double newMeters;
	    long earthRadius = 6378137;
	    
	    newMeters = (double)2*meters;
	    myLat = position.getLatGps();
	    myLon = position.getLongGps();
	    upperLat = myLat + (180/Math.PI)*(newMeters/earthRadius);
	    lowerLat = myLat - (180/Math.PI)*(newMeters/earthRadius);
	    
	    if(Math.cos(myLat)>=0){
	    	upperLon = myLon + (180/Math.PI)*(newMeters/(earthRadius*Math.cos(myLat)));
	    	lowerLon = myLon - (180/Math.PI)*(newMeters/(earthRadius*Math.cos(myLat)));
	    }
	    else
	    {
	    	upperLon = myLon - (180/Math.PI)*(newMeters/(earthRadius*Math.cos(myLat)));
	    	lowerLon = myLon + (180/Math.PI)*(newMeters/(earthRadius*Math.cos(myLat)));
	    }
	    
		try {
			OpenDb();
			
	        st = connection.prepareStatement("SELECT offer.ID, Category, Product, StartDate, EndDate, InfoProduct, Price, ShopName, ShopAddress, LongitudePosition, LatitudePosition FROM offer JOIN merchant ON offer.IdMerchant = merchant.ID WHERE IdMerchant IN (SELECT ID FROM merchant WHERE ((LongitudePosition BETWEEN ? AND ?) AND (LatitudePosition BETWEEN ? AND ?) AND Category IN (?, ?, ?, ?, ?)))");
	        st.setDouble(1, lowerLon);
	        st.setDouble(2, upperLon);
	        st.setDouble(3, lowerLat);
	        st.setDouble(4, upperLat);
	        st.setString(5, preferences.get(0));
	        st.setString(6, preferences.get(1));
	        st.setString(7, preferences.get(2));
	        st.setString(8, preferences.get(3));
	        st.setString(9, preferences.get(4));
	        
	        rs = st.executeQuery();
	        
	        while(rs.next())
	        {
	        	Merchant singleMerchant = new Merchant(rs.getDouble("LongitudePosition"), rs.getDouble("LatitudePosition"), rs.getString("ShopName"), rs.getString("ShopAddress"));
	        	Offer singleOffer = new Offer(rs.getInt("ID"), rs.getString("Category"), rs.getString("Product"), rs.getString("InfoProduct"), rs.getDate("StartDate"), rs.getDate("EndDate"), rs.getDouble("Price"));
	        	Entry<Merchant, Offer> newEntry = new AbstractMap.SimpleEntry<Merchant, Offer>(singleMerchant, singleOffer);
	        	
	        	list.add(newEntry);
	        }
	        
	        st.close();
		}
	    catch(SQLException e)
	    {
	    	e.printStackTrace();
	    }
		finally
		{
			CloseDb();
		}
		
		
		return list;
	}
	

	public boolean UpdateUsersStatistics(){
		boolean updated = false;
		Statement st;
		
		try {
			OpenDb();
			
			st = connection.createStatement();
	        st.executeUpdate("UPDATE userstatistics SET usersCount = usersCount+1");
	        
	        updated = true;
	        
	        st.close();
		}
	    catch(SQLException e)
	    {
	    	e.printStackTrace();
	    }
		finally
		{
			CloseDb();
		}
		
		return updated;
	}
}
